﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlServerCe;

namespace palmApp.Database
{
    public class Manager
    {
        private String connectionString = ("Data Source =" + (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\Database\\altrondb.sdf;"));
        private SqlCeConnection conn;
        private SqlCeCommand command;

        private DataSet ds;

        public Manager()
        {
        }

        public void Query(String str)
        {
            /*
             * using (conn = new SqlCeConnection(connectionString))
            {
                conn.Open();
                command = new SqlCeCommand(str, conn);
            }
             */

            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = connectionString;
                conn.Open();
                command = new SqlCeCommand(str, conn);
            }
            catch (ObjectDisposedException ode)
            {
            }
        }

        public SqlCeCommand Command
        {
            set { this.command = value; }
            get { return this.command; }
        }

        public DataSet Ds
        {
            get { return this.ds; }
        }

        /// <summary>
        /// Construye DataSet para su posterior manipulación.
        /// </summary>
        /// <param name="arrStr">Array de tablas para construir DataSet.</param>
        /// <param name="dsName">Nombre del DataSet.</param>
        public void BuildDataSet(String[] arrStr, String dsName)
        {
            this.ds = new DataSet(dsName);
            using (conn = new SqlCeConnection(connectionString))
            {
                foreach (String tableName in arrStr)
                {
                    SqlCeDataAdapter da;
                    
                    da = new SqlCeDataAdapter("SELECT * FROM " + tableName, conn);
                    da.FillSchema(this.ds, SchemaType.Source, tableName);
                    da.Fill(this.ds, tableName);
                    
                    da.Dispose();
                    da = null;
                }
            }
        }

        public DataSet operationMethod()
        {
            DataSet dsOperation = new DataSet("operation");

            using (conn = new SqlCeConnection(connectionString))
            {
                SqlCeDataAdapter daOperation = new SqlCeDataAdapter("SELECT * FROM machines", conn);
                daOperation.FillSchema(dsOperation, SchemaType.Source, "machines");
                daOperation.Fill(dsOperation, "machines");

                daOperation = new SqlCeDataAdapter("SELECT * FROM owner", conn);
                daOperation.FillSchema(dsOperation, SchemaType.Source, "owner");
                daOperation.Fill(dsOperation, "owner");

                daOperation = new SqlCeDataAdapter("SELECT * FROM operation", conn);
                daOperation.FillSchema(dsOperation, SchemaType.Source, "operation");
                daOperation.Fill(dsOperation, "operation");

                daOperation.Dispose();
                daOperation = null;
            }

            return (dsOperation);
        }

        public Int32 NewOperation(Object[] objArr)
        {
            Int32 result = -1;

            using(conn = new SqlCeConnection(connectionString))
            {
                conn.Open();

                SqlCeTransaction trans = conn.BeginTransaction();

                String sqlStr = "INSERT INTO operation (operation_type, operation_date, operation_status, diagnosis, changed_parts, comtec_id_machine, comtec_id_responsible, next_maintainment_date, maintainment_period, maintainment_period_date, maintainment_status) VALUES (@operation_type, @operation_date, @operation_status, @diagnosis, @changed_parts, @comtec_id_machine, @comtec_id_responsible, @next_maintainment_date, @maintainment_period, @maintainment_period_date, @maintainment_status)";

                command = new SqlCeCommand(sqlStr, conn, trans);
                
                command.Parameters.Add("@operation_type", SqlDbType.Int);
                command.Parameters["@operation_type"].Value = objArr[10];
                
                command.Parameters.Add("@operation_date", SqlDbType.DateTime);
                command.Parameters["@operation_date"].Value = objArr[9];
                
                command.Parameters.Add("@operation_status", SqlDbType.Int);
                command.Parameters["@operation_status"].Value = objArr[8];

                command.Parameters.Add("@diagnosis", SqlDbType.NVarChar, 400);
                command.Parameters["@diagnosis"].Value = objArr[7];

                command.Parameters.Add("@changed_parts", SqlDbType.NVarChar, 400);
                command.Parameters["@changed_parts"].Value = objArr[6];
                
                command.Parameters.Add("@comtec_id_machine", SqlDbType.Int);
                command.Parameters["@comtec_id_machine"].Value = objArr[5];
                
                command.Parameters.Add("@comtec_id_responsible", SqlDbType.Int);
                command.Parameters["@comtec_id_responsible"].Value = objArr[4];
                
                command.Parameters.Add("@next_maintainment_date", SqlDbType.DateTime);
                command.Parameters["@next_maintainment_date"].Value = objArr[3];
                
                command.Parameters.Add("@maintainment_period", SqlDbType.Int);
                command.Parameters["@maintainment_period"].Value = objArr[2];

                command.Parameters.Add("@maintainment_period_date", SqlDbType.NVarChar, 400);
                command.Parameters["@maintainment_period_date"].Value = objArr[1];
                
                command.Parameters.Add("@maintainment_status", SqlDbType.Int);
                command.Parameters["@maintainment_status"].Value = objArr[0];

                try
                {
                    result = command.ExecuteNonQuery();
                    trans.Commit();
                    command.Parameters.Clear();
                }
                catch (SqlCeException ex)
                {
                    trans.Rollback();
                }

                return (result);
            }
        }

        public Int32 NewResponsible(Object[] objArr)
        {
            Int32 result = -1;

            using (conn = new SqlCeConnection(connectionString))
            {
                conn.Open();

                SqlCeTransaction trans = conn.BeginTransaction();

                String sqlStr = "INSERT INTO responsible (tag_id_responsible, name, lastname1, lastname2, rut, charge, income_date, birthdate, notes, cod_mainteiner) VALUES (@tag_id_responsible, @name, @lastname1, @lastname2, @rut, @charge, @income_date, @birthdate, @notes, @cod_mainteiner)";

                command = new SqlCeCommand(sqlStr, conn, trans);

                command.Parameters.Add("@tag_id_responsible", SqlDbType.NVarChar, 10);
                command.Parameters["@tag_id_responsible"].Value = objArr[9];
                
                command.Parameters.Add("@name", SqlDbType.NVarChar, 50);
                command.Parameters["@name"].Value = objArr[8];

                command.Parameters.Add("@lastname1", SqlDbType.NVarChar, 50);
                command.Parameters["@lastname1"].Value = objArr[7];

                command.Parameters.Add("@lastname2", SqlDbType.NVarChar, 50);
                command.Parameters["@lastname2"].Value = objArr[6];

                command.Parameters.Add("@rut", SqlDbType.NVarChar, 11);
                command.Parameters["@rut"].Value = objArr[5];

                command.Parameters.Add("@charge", SqlDbType.NVarChar, 150);
                command.Parameters["@charge"].Value = objArr[4];
                
                command.Parameters.Add("@income_date", SqlDbType.DateTime);
                command.Parameters["@income_date"].Value = objArr[3];

                command.Parameters.Add("@birthdate", SqlDbType.DateTime);
                command.Parameters["@birthdate"].Value = objArr[2];

                command.Parameters.Add("@notes", SqlDbType.NVarChar, 200);
                command.Parameters["@notes"].Value = objArr[1];

                command.Parameters.Add("@cod_mainteiner", SqlDbType.NVarChar, 10);
                command.Parameters["@cod_mainteiner"].Value = objArr[0];

                try
                {
                    result = command.ExecuteNonQuery();
                    trans.Commit();
                    command.Parameters.Clear();
                }
                catch (SqlCeException ex)
                {
                    trans.Rollback();
                }

                return (result);
            }
        }
        /*
         * 
        public Int32 newMachine(Object[] objArr)
        {
            Int32 result = -1;

            using (conn = new SqlCeConnection(connectionString))
            {
                conn.Open();

                SqlCeTransaction trans = conn.BeginTransaction();

                String sqlStr = "INSERT INTO machines (tag_id_machine, cod_mainteiner, cod_owner, brand, model, year, notes, comtec_id_owner, comtec_id_mainteiner) VALUES (@tag_id_machine, @cod_mainteiner, @cod_owner, @brand, model, @year, @notes, @comtec_id_owner, @comtec_id_mainteiner)";

                command = new SqlCeCommand(sqlStr, conn, trans);

                command.Parameters.Add("@tag_id_machine", SqlDbType.NVarChar, 10);
                command.Parameters["@tag_id_machine"].Value = objArr[];

                command.Parameters.Add("@cod_mainteiner", SqlDbType.NVarChar, 10);
                command.Parameters["@cod_mainteiner"].Value = objArr[];
                
                command.Parameters.Add("@cod_owner", SqlDbType.NVarChar, 10);
                command.Parameters["@cod_owner"].Value = objArr[];

                command.Parameters.Add("@brand", SqlDbType.NVarChar, 20);
                command.Parameters["@brand"].Value = objArr[];
                
                command.Parameters.Add("@model", SqlDbType.Int);
                command.Parameters["@model"].Value = objArr[];

                command.Parameters.Add("@year", SqlDbType.NVarChar, 200);
                command.Parameters["@year"].Value = objArr[];
                
                command.Parameters.Add("@notes", SqlDbType.Int);
                command.Parameters["@notes"].Value = objArr[];
                
                command.Parameters.Add("@comtec_id_owner", SqlDbType.Int);
                command.Parameters["@comtec_id_owner"].Value = objArr[];
                
                command.Parameters.Add("@comtec_id_mainteiner", SqlDbType.Int);
                command.Parameters["@comtec_id_mainteiner"].Value = objArr[];

                try
                {
                    result = command.ExecuteNonQuery();
                    trans.Commit();
                    command.Parameters.Clear();
                }
                catch (SqlCeException ex)
                {
                    trans.Rollback();
                }

                return (result);
            }
        }
         * */

        public void Close()
        {
            if (command != null)
            {
                command.Dispose();
            }
            conn.Close();
            conn.Dispose();
        }
    }
}
